package app

import (
	"fmt"
	"strings"
	"time"

	"git.sr.ht/~mil/mobsql/logger"
	"git.sr.ht/~mil/mobsql/sqlgen"
	"git.sr.ht/~mil/mobsql/utilfuncs"
)

// SourcesetCompute recomputes the computed tables for the given sourceset
func SourcesetCompute(m *MDB, sourceset []int) (*SourcesetOpResult, error) {
	logger.Infof("Running Compute against %d sources", len(sourceset))
	defer logger.Infof("Running Compute against %d sources ok", len(sourceset))

	for _, t := range m.Schema.TablesComputed {
		if t.SourcePair {
			computeSources := utilfuncs.IntArraysSortedFilter(utilfuncs.Pairs(sourceset))
			for _, multiSource := range computeSources {
				if err := m.computeTableForSource(&t, multiSource[0], &multiSource[1]); err != nil {
					return nil, err
				}
			}
		} else {
			for _, singleSource := range sourceset {
				if err := m.computeTableForSource(&t, singleSource, nil); err != nil {
					return nil, err
				}
			}
		}
	}

	return &SourcesetOpResult{
		Operation: "compute",
		Success:   true,
		Mdbids:    sourceset,
	}, nil
}

func (m *MDB) computeTableForSource(table *sqlgen.ComputedTable, source int, sourcePair *int) error {
	if computedTableShaWant, err := m.QueryValue(
		fmt.Sprintf(
			`select group_concat(source || ":" || tbl || ":" || checksum) from _mobsql
					where (source = ? or (? is not null and source = ?)) and tbl in (%s) order by source, tbl`,
			strings.Join(utilfuncs.ArrayWithValue(len(table.CacheOnTables), "?"), ","),
		),
		append([]any{source, sourcePair, sourcePair}, utilfuncs.Anyify(table.CacheOnTables)...),
	); err != nil {
		return fmt.Errorf("Error querying computed table want sha: %w", err)
	} else if computedTableShaLast, err := m.QueryValue(
		"select checksum from _mobsql where source = ? and (? is null or source_pair = ?) and tbl = ?",
		[]any{source, sourcePair, sourcePair, table.TableSpec.Table},
	); err != nil {
		return fmt.Errorf("Error querying computed table has sha: %w", err)
	} else if computedTableShaLast == computedTableShaWant {
		logger.Infof("Computed table %s for source (%d %v) already up to date", table.TableSpec.Table, source, sourcePair)
	} else {
		logger.Infof("Computed table %s for source (%d %v) out of date - updating ('%s' vs '%s')", table.TableSpec.Table, source, sourcePair, computedTableShaLast, computedTableShaWant)
		return m.computeTableForSourceUpdate(table, source, sourcePair, computedTableShaWant)
	}
	return nil
}

func (m *MDB) computeTableForSourceUpdate(table *sqlgen.ComputedTable, source int, sourcePair *int, logShasum string) error {
	var (
		statementDelete = computeDeleteGenerateSQL(source, sourcePair, table)
		statementInsert = computeInsertGenerateSQL(source, sourcePair, table)
	)

	if _, err := m.DB.Exec(statementDelete, source, sourcePair); err != nil {
		return fmt.Errorf("Error executing delete statement (%s) for computed table (%v): %w", statementDelete, table.Name, err)
	} else if _, err := m.DB.Exec(statementInsert, source, sourcePair); err != nil {
		return fmt.Errorf("Error executing insert statement (%s) for computed table (%v): %w", statementInsert, table.Name, err)
	} else if _, err := m.DB.Exec(
		`insert into _mobsql (source, source_pair, tbl, checksum, n_rows, timestamp) values(?, ?, ?, ?, ?, ?)
		 on conflict do update set checksum = ?, n_rows = ?, timestamp = ?`,
		source, sourcePair, table.TableSpec.Table,
		logShasum, 999, time.Now().Unix(),
		logShasum, 999, time.Now().Unix(),
	); err != nil {
		return fmt.Errorf("Error marking computed table record in _mobsql table: %w", err)
	} else {
		return nil
	}
}

func computeInsertGenerateSQL(source int, sourcePair *int, table *sqlgen.ComputedTable) string {
	var (
		base        string
		columnNames = []string{}
	)
	for _, col := range table.TableSpec.Columns {
		columnNames = append(columnNames, col.Column)
	}
	if sourcePair == nil {
		base = "insert into %s (%s) select %s from %s where source = ?"
	} else {
		base = "insert into %s (%s) select %s from %s where source = ? and source_pair = ?"
	}
	return fmt.Sprintf(
		base,
		table.TableSpec.Table,
		strings.Join(columnNames, ","),
		strings.Join(columnNames, ","),
		table.View,
	)
}

func computeDeleteGenerateSQL(source int, sourcePair *int, table *sqlgen.ComputedTable) string {
	if sourcePair == nil {
		return fmt.Sprintf("delete from %s where source = ?", table.TableSpec.Table)
	} else {
		return fmt.Sprintf("delete from %s where source = ? and source_pair = ?", table.TableSpec.Table)
	}
}
